/*
 * Copyright(C) 2010 Luvina Software Company
 *
 * TestDaoImpl.java, Oct 29, 2010 ntmhuong
 */
package net.luvina.manageuser.dao.impl;

import java.util.List;

import net.luvina.manageuser.dao.TestDao;
import net.luvina.manageuser.dto.TestDto;
import net.luvina.manageuser.hibernate.User;
import net.luvina.manageuser.hibernate.UserGrp;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

/**
 * TestDaoImpl
 *
 * @author ntmhuong
 *
 */
@Repository("userDao")
public class TestDaoImpl extends GenericDaoImpl<User, Integer> implements
        TestDao {
    public TestDaoImpl() {
        super(User.class);
    }

    /*
     * (non-Javadoc)
     *
     * @see net.luvina.manageuser.dao.TestDao#findUser()
     */
    @SuppressWarnings("unchecked")
    public List<TestDto> findUser() {
        /*
         * HibernateCallback callback = new HibernateCallback() { public Object
         * doInHibernate(Session session) throws HibernateException { Query
         * query = session.createQuery("FROM User us " +
         * "INNER JOIN FETCH us.userGrp ug "); return (List<User>) query.list();
         * } }; return (List<User>) getHibernateTemplate().execute(callback);
         */
        HibernateCallback callback = new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException {
                StringBuilder sqlCommand = new StringBuilder("");
                sqlCommand = sqlCommand.append(" SELECT  ");
                sqlCommand = sqlCommand.append(" u.user_id as userId, ");
                sqlCommand = sqlCommand.append(" u.user_name as userName, ");
                sqlCommand = sqlCommand.append(" g.user_grp_id as userGrpId, ");
                sqlCommand = sqlCommand
                        .append(" g.user_grp_name as userGrpName ");
                sqlCommand = sqlCommand.append(" FROM tbl_user as u ");
                sqlCommand = sqlCommand.append(" INNER JOIN user_grp as g ");
                sqlCommand = sqlCommand
                        .append(" ON u.user_grp_id = g.user_grp_id ");
                System.out.println("sqlCommand = " + sqlCommand.toString());
                Query query = session.createSQLQuery(sqlCommand.toString())
                        .addScalar("userId", Hibernate.INTEGER).addScalar(
                                "userName", Hibernate.STRING).addScalar(
                                "userGrpId", Hibernate.INTEGER).addScalar(
                                "userGrpName", Hibernate.STRING)
                        .setResultTransformer(
                                Transformers.aliasToBean(TestDto.class));
                // query.addScalar();
                return (List<TestDto>) query.list();

            }
        };
        return (List<TestDto>) getHibernateTemplate().execute(callback);
    }

    /*
     * (non-Javadoc)
     *
     * @see net.luvina.manageuser.dao.TestDao#getGroupList()
     */
    @SuppressWarnings("unchecked")
    public List<UserGrp> getGroupList() {
        HibernateCallback callback = new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException {
                Query query = session.createQuery("FROM UserGrp ");
                return (List<UserGrp>) query.list();
            }
        };
        return (List<UserGrp>) getHibernateTemplate().execute(callback);
    }

    /*
     * (non-Javadoc)
     *
     * @see net.luvina.manageuser.dao.TestDao#getListUser(int, int, int,
     * java.lang.String)
     */
    @SuppressWarnings("unchecked")
    public List<User> getListUser(final int offset, final int limit,
            final int userGrpId, final String userName) {
        HibernateCallback callback = new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException {
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append(" FROM User u ");
                sqlBuilder.append(" INNER JOIN FETCH u.userGrp ug ");
                if (userGrpId > 0 && (userName != null && userName != "")) {
                    sqlBuilder.append(" WHERE ug.userGrpId=:userGrpId");
                    sqlBuilder.append(" AND u.userName LIKE :userName");
                } else {
                    if (userName != null && userName != "") {
                        sqlBuilder.append(" WHERE u.userName LIKE :userName");
                    }
                    if (userGrpId > 0) {
                        sqlBuilder.append(" WHERE ug.userGrpId=:userGrpId");
                    }
                }
                Query query = session.createQuery(sqlBuilder.toString());
                query.setFirstResult(offset);
                query.setMaxResults(limit);
                if (userGrpId > 0 && (userName != null && userName != "")) {
                    query.setString("userGrpId", String.valueOf(userGrpId));
                    query.setString("userName", "%" + userName + "%");
                } else {
                    if (userName != null && userName != "") {
                        query.setString("userName", "%" + userName + "%");
                    }
                    if (userGrpId > 0) {
                        query.setString("userGrpId", String.valueOf(userGrpId));
                    }
                }
                return (List<User>) query.list();
            }
        };
        return (List<User>) getHibernateTemplate().execute(callback);
    }

    /*
     * (non-Javadoc)
     *
     * @see net.luvina.manageuser.dao.TestDao#getTotalRecords(int,
     * java.lang.String)
     */
    @SuppressWarnings("unchecked")
    public int getTotalRecords(final int userGrpId, final String userName) {
        HibernateCallback callback = new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException {
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append(" FROM User u ");
                sqlBuilder.append(" INNER JOIN FETCH u.userGrp ug ");
                if (userGrpId > 0 && (userName != null && userName != "")) {
                    sqlBuilder.append(" WHERE ug.userGrpId=:userGrpId");
                    sqlBuilder.append(" AND u.userName LIKE :userName");
                } else {
                    if (userName != null && userName != "") {
                        sqlBuilder.append(" WHERE u.userName LIKE :userName");
                    }
                    if (userGrpId > 0) {
                        sqlBuilder.append(" WHERE ug.userGrpId=:userGrpId");
                    }
                }
                Query query = session.createQuery(sqlBuilder.toString());

                if (userGrpId > 0 && (userName != null && userName != "")) {
                    query.setString("userGrpId", String.valueOf(userGrpId));
                    query.setString("userName", "%" + userName + "%");
                } else {
                    if ((userName != null && userName != "")) {
                        query.setString("userName", "%" + userName + "%");
                    }
                    if (userGrpId > 0) {
                        query.setString("userGrpId", String.valueOf(userGrpId));
                    }
                }
                List<User> userList = query.list();
                if (userList != null && userList.size() > 0) {
                    return userList.size();
                } else {
                    return 0;
                }
            }
        };
        return (Integer) getHibernateTemplate().execute(callback);
    }
}